package com.osi.mes.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.datasweep.compatibility.ui.Time;
import com.osi.mes.utils.StringUtils;


public class ExcelPoiParser
{
	private static Log logger = LogFactory.getLog(ExcelPoiParser.class);
	public static int ingnoreTopRowNumber =1 ;
	public static int ingnoreBottomRowNumber =0 ;
	public static String DATE_FORMAT1 = "";
	public static String DATE_FORMAT2 = "";

	public static List parseExcelFile(String excelFileFullPath, String rowObjectClassName)
		throws ExcelParserException, IOException
	{
		return parseExcelFile(
			excelFileFullPath, 0, rowObjectClassName);
	}

	public static List parseExcelFile(String excelFileFullPath, int sheetIndex,
		String rowObjectClassName)
		throws ExcelParserException, IOException
	{
		File excelFile = new File(excelFileFullPath);
		if (!excelFile.exists())
		{
			String err_msg = "Invalid Excel File Path :" + excelFileFullPath;
			logger.error(err_msg);
			throw new ExcelParserException(err_msg);
		}
		return parseExcelFile(
			excelFile, sheetIndex, rowObjectClassName);
	}

	public static List parseExcelFile(File excelFile, String rowObjectClassName)
		throws ExcelParserException, IOException
	{
		return parseExcelFile(
			excelFile, 0, rowObjectClassName);
	}
	
	public static List parseExcelFile(File excelFile,String rowObjectClassName,int ignoreTopRows,int ingnoreBottomRows)
	throws ExcelParserException, IOException
	{
		InputStream excelFileStream;
		try
		{
			excelFileStream = new FileInputStream(excelFile);
		}
		catch (FileNotFoundException e)
		{
			String err_msg = "File Not Found :" + excelFile.getAbsolutePath();
			logger.error(err_msg);
			throw new ExcelParserException(err_msg);
		}
		
	    return parseExcelFile(
			excelFileStream, 0, rowObjectClassName, ignoreTopRows,ingnoreBottomRows);
	}

	public static List parseExcelFile(File excelFile, int sheetIndex, String rowObjectClassName)
		throws ExcelParserException, IOException
	{
		InputStream excelFileStream;
		try
		{
			excelFileStream = new FileInputStream(excelFile);
		}
		catch (FileNotFoundException e)
		{
			String err_msg = "File Not Found :" + excelFile.getAbsolutePath();
			logger.error(err_msg);
			throw new ExcelParserException(err_msg);
		}
		return parseExcelFile(
			excelFileStream, sheetIndex, rowObjectClassName);
	}

	public static List parseExcelFile(InputStream excelFileStream, String rowObjectClassName)
		throws ExcelParserException, IOException
	{
		return parseExcelFile(
			excelFileStream, 0, rowObjectClassName);
	}

	public static List parseExcelFile(InputStream excelFileStream, int sheetIndex,
		String rowObjectClassName)
		throws ExcelParserException, IOException
	{
		return parseExcelFile(
			excelFileStream, sheetIndex, rowObjectClassName, ingnoreTopRowNumber,ingnoreBottomRowNumber);
	}

	public static List parseExcelFile(InputStream excelFileStream, int sheetIndex,
		String rowObjectClassName, int ignoreTopRows,int ingnoreBottomRowNumber)
		throws ExcelParserException, IOException
	{
		List rowObjectList = new ArrayList();
		Class rowObjectClass;

		HSSFWorkbook workbook;
		try
		{
			rowObjectClass = Class.forName(rowObjectClassName);
			workbook = new HSSFWorkbook(excelFileStream);
			HSSFSheet sheet0 = workbook.getSheetAt(sheetIndex); // sheet index
			int fromRowNum = sheet0.getFirstRowNum() + ignoreTopRows; // ingore rows
			int toRowNum = sheet0.getLastRowNum()- ingnoreBottomRowNumber; // to last row
			HSSFRow headrow = sheet0.getRow(sheet0.getFirstRowNum());
			for (int i = fromRowNum; i <= toRowNum; i++)
			{
				HSSFRow row = sheet0.getRow(i);
				BaseRowObject rowObj = getRowObject(
					headrow, row, rowObjectClass); // convert to obj
				rowObjectList.add(rowObj);
			}
			excelFileStream.close();
		}
		catch (IOException e)
		{
			excelFileStream.close();
			String err_msg = "IO Exception occured when read stream :" + excelFileStream;
			logger.error(err_msg);
			throw new ExcelParserException(err_msg);
		}
		catch (ClassNotFoundException e)
		{
			e.printStackTrace();
			String err_msg = "Class Not Found :" + rowObjectClassName;
			logger.error(err_msg);
			throw new ExcelParserException(err_msg);
		}
		finally
		{
			if(excelFileStream!=null)
				excelFileStream.close();
		}
		
		return rowObjectList;
	}

	private static BaseRowObject getRowObject(HSSFRow headrow, HSSFRow row, Class rowObjectClass)
		throws ExcelParserException
	{
		// get the KBObject Class Name and instance
		BaseRowObject rowObject;
		try
		{
			rowObject = (BaseRowObject) rowObjectClass.newInstance();
		}
		catch (Exception e)
		{
			String err_msg = "Error occured when creating row object :" + rowObjectClass.getName();
			logger.error(err_msg,e);
			throw new ExcelParserException(err_msg);
		}

		// set the KBObject properties
		for (int i = headrow.getFirstCellNum(); i < headrow.getLastCellNum(); i++)
		{
			HSSFCell cell = row.getCell((short) i);
			setRowObjectField(
				rowObject, cell, i);
		}

		return rowObject;
	}

	private static void setRowObjectField(BaseRowObject rowObject, HSSFCell cell, int cellIndex)
		throws ExcelParserException
	{
		String fieldName = "";
		Object value = "";
		try
		{
			// prepare the setter method's param info
			if (rowObject.getPropertyName(cellIndex) != null)
			{
				if (cell != null)
				{
					fieldName = rowObject.getPropertyName(cellIndex);
					if(StringUtils.isNull(fieldName))
						return ;
					
					Class propertyType = String.class;
					
					propertyType = PropertyUtils.getPropertyType(
							rowObject, fieldName);
					Object anotherDateVal;
					java.text.DecimalFormat formatter = new java.text.DecimalFormat("######## ");
					
					switch (cell.getCellType())
					{
						case HSSFCell.CELL_TYPE_STRING:
							value = cell.getRichStringCellValue().toString();
							break;
						case HSSFCell.CELL_TYPE_NUMERIC:
								try
								{
								value = new BigDecimal( cell.getNumericCellValue());
								if (propertyType.isAssignableFrom(Date.class))
								{
									value =formatter.format(value);
								}
								else if (propertyType.isAssignableFrom(String.class))
								{
									value = formatter.format(value);
								}
							}
								catch(Exception e)
								{
									value = new BigDecimal(cell.getNumericCellValue());
								}
								
							break;
						case HSSFCell.CELL_TYPE_BOOLEAN:
							value =cell.getBooleanCellValue();
							break;
						case HSSFCell.CELL_TYPE_BLANK:
							return;
					}
					
					if (propertyType.isAssignableFrom(Time.class))
					{
						String timeStr = formatTimeStr(value.toString());
						try
						{
							value = new SimpleDateFormat(DATE_FORMAT1).parse(timeStr);
							
							if (value == null)
								value = new SimpleDateFormat(DATE_FORMAT2).parse(timeStr);
						}
						catch(ParseException e)
						{
							value = cell.getDateCellValue();
						}
						
						Calendar cal = Calendar.getInstance();
						cal.setTime((Date)value);	
						setDateProperty(
							rowObject, fieldName, new Time(cal));
					}
					else
					{
						if(value instanceof String)
						{
							String val = value.toString().trim();
							val = val.replace("\t", "");
							val = val.replace("\"", "");
							value = val.trim();
						}
						
						BeanUtils.setProperty(
							rowObject, fieldName,value);
					}
				}
			}
		}
		catch(Exception e)
		{
			String errorMsg = "setRowObjectField failed. fieldName: "+fieldName +" fieldValue:"+value+"  cellIndex:"+cellIndex;;
			logger.error(errorMsg);
			e.printStackTrace();
			throw new ExcelParserException(errorMsg);
		}
	}
				
	private static void setDateProperty(Object bean, String propertyName, Time time)
		throws NoSuchMethodException, IllegalAccessException, InvocationTargetException
	{
		StringBuffer sb = new StringBuffer();
		char[] ch = propertyName.toCharArray();
		sb.append(Character.toTitleCase(ch[0]));

		for (int y = 1; y < ch.length; y++)
		{
			sb.append(ch[y]);
		}

		String proName = "set" + sb.toString();
		Class clazz = bean.getClass();
		Method method = clazz.getMethod(
			proName, new Class[] { Time.class });
		method.setAccessible(true);
		method.invoke(
			bean, new Time[] { time });
	}
	
	private static String formatTimeStr(String strDatetime)
	{
		if(StringUtils.isNull(strDatetime))
			return "";
		
		if(strDatetime.contains("-"))
		{
			String[] times = strDatetime.split("-");
			if(times[1].length()<2)
				times[1] = "0"+times[1];
			if(times[2].length()<2)
				times[2] = "0"+times[3];
			
			strDatetime = times[0]+"-"+times[1]+"-"+times[2];
		}
		
		return strDatetime;
	}

	public static Date convert2ExcelDate(int days)
	{
		Calendar calendar = Calendar.getInstance();
		calendar.set(
			1900, 0, 0, 0, 0, 0);
		calendar.add(
			Calendar.DAY_OF_MONTH, days - 1);

		return calendar.getTime();
	}

}